import os

import pymysql as sql
import sql_handler.config as cf


class handler:
    def __init__(self):
        self.connect = None
        self.cursor = None

    def connect_sql_from_config(self):
        self.connect = sql.connect(host=cf.host,
                                   user=cf.username,
                                   password=cf.password,
                                   database=cf.database,
                                   autocommit=True,
                                   charset=cf.charset)
        self.cursor = self.connect.cursor()

    def connect_sql(self, user, pwd):
        self.connect = sql.connect(host=cf.host,
                                   user=user,
                                   password=pwd,
                                   database=cf.database,
                                   autocommit=True,
                                   charset=cf.charset)
        self.cursor = self.connect.cursor()

    def initDatabase(self):
        # print(os.getcwd())
        self.create_tables()
        print("init for tables:", end="")
        self.show_tables()

    def initData(self):
        pass

    def initTrigger(self):
        pass

    def initProcedure(self):
        pass

    def initView(self):
        pass

    def show_tables(self):
        print(self.cursor.execute("show tables;"))
        t = self.cursor.fetchall()
        print(t)
        return t

    def show_table(self, table):
        self.cursor.execute(f"select * from {table};")
        print(self.cursor.fetchall())

    def drop_table(self, table_name):
        sql_ = "drop table if exists " + table_name
        self.cursor.execute(sql_)

    def create_tables(self):
        pass

    def insert_into_room(self, _id, _price, _type):
        sql_ = f"insert into room (roomID, price, type) values({_id}, {_price}, '{_type}')"
        self.cursor.execute(sql_)
        self.connect.commit()

    def update_room_price(self, _id, _price):
        sql_ = f"update room set price='{_price}' where roomID='{_id}'"
        self.cursor.execute(sql_)
        self.connect.commit()

    def update_room_type(self, _id, _type):
        sql_ = f"update room set type='{_type}' where roomID='{_id}'"
        self.cursor.execute(sql_)
        self.connect.commit()

    def delete_from_room(self, _id):
        sql_ = f"delete from room where roomID={_id}"
        self.cursor.execute(sql_)
        self.connect.commit()

    # 插入顾客的同时检查是否有信息变更，有变更会进行弹窗提示
    def insert_into_customer(self, _id, _name, _sex, _telephone):
        sql_ = f"insert into customer values('{_id}', '{_name}', '{_sex}', '{_telephone}')"
        try:
            self.cursor.execute(sql_)
        except:
            sql_ = f"select * from customer where cid='{_id}'"
            self.cursor.execute(sql_)
            ans = self.cursor.fetchall()
            if ans[0][1] != _name or ans[0][2] != _sex or ans[0][3] != _telephone:
                sql_ = f"update customer set name='{_name}', sex='{_sex}', tel='{_telephone}' where cid='{_id}'"
                return "change"
        pass

    def run(self):
        pass

    # 查询所有客人的信息
    def select_all_customer(self):
        sql_ = "select c.cid, c.name, c.sex, c.tel, rrid, rtype, rprice, sein, seout " \
               "from customer c left join (" \
               "select r.roomid rrid, r.type rtype, r.price rprice, se.checkin sein, se.checkout seout, st.cid stcid " \
               "from room r, stay st, sequenceinfo se " \
               "where r.roomid = st.roomid and st.serialnum = se.serialnum) t " \
               "on c.cid = stcid"
        print(sql_)
        self.cursor.execute(sql_)
        t = self.cursor.fetchall()
        return t

    # 仅查询顾客基本信息
    def select_customer_basic_info_by_id(self, cid):
        sql_ = f"select cid, name, sex, tel from customer where cid = '{cid}'"
        self.cursor.execute(sql_)
        t0 = self.cursor.fetchall()
        return t0

    # 按照客人id查询该客人的信息
    def select_customer_information_by_id(self, cid):
        sql_ = f"select cid, name, sex, tel from customer where cid = {cid}"
        self.cursor.execute(sql_)
        t0 = self.cursor.fetchall()
        if len(t0) == 0:
            return []
        sql_ = "select c.cid, c.name, c.sex, c.tel, r.roomID, r.type, r.price, se.checkin, se.checkout " \
               "from customer c, room r, stay st, sequenceInfo se " \
               f"where c.cid = st.cid and st.serialNum = se.serialNum and st.roomID = r.roomID and c.cid = {cid}"
        self.cursor.execute(sql_)
        t = self.cursor.fetchall()
        if len(t) == 0:
            return [[t0[0][0], t0[0][1], t0[0][2], t0[0][3], "None", "None", "None", "None", "None"]]
        return t

    # 查询客人预订信息
    def select_customer_info_by_id_and_date(self, cid, date):
        sql_ = f"select s.serialnum, c.cid, c.name, c.tel from customer c, sequenceinfo s " \
               f"where c.cid='{cid}' and c.cid = s.leadercid and s.wantdate='{date}'" \
               f" and s.success=true and s.hasPay=false"
        self.cursor.execute(sql_)
        t = self.cursor.fetchall()
        return t

    # 查询客人已入住的信息，准备退房
    def select_needCheckout(self, cid, today):
        sql_ = f"select s.serialnum, c.cid, c.name, c.tel, f.prices from customer c, sequenceinfo s, financial f " \
               f"where c.cid='{cid}' and c.cid = s.leadercid and s.wantdate <= '{today}'" \
               f"and f.serialNum=s.serialNum "\
               f" and s.success=true and s.hasPay=True and checkout is null "
        self.cursor.execute(sql_)
        t = self.cursor.fetchall()
        return t

    # 根据流水号查询所有客人信息
    def select_infos_by_serialnum(self, serial_num):
        sql_ = "select r.type, r.roomid, r.price, c.cid, c.name, c.sex, c.tel " \
               "from " \
               "stay natural join customer c natural join room r " \
              f"where r.type='单人房' and stay.serialnum='{serial_num}'"
        self.cursor.execute(sql_)
        ret = self.cursor.fetchall()

        sql_ = "select r.type, r.roomid, r.price, s1cid, s1name, s1sex, s1tel, " \
               "s2cid, s2name, s2sex, s2tel from room r natural join " \
               "(select s1.roomid, s1.cid s1cid, s1.name s1name, s1.sex s1sex, s1.tel s1tel, " \
               "s2.cid s2cid, s2.name s2name, s2.sex s2sex, s2.tel s2tel " \
               "from " \
               f"(select * from stay natural join customer where serialnum='{serial_num}') s1," \
               f"(select * from stay natural join customer where serialnum='{serial_num}') s2 " \
               "where s1.cid < s2.cid and s1.roomid = s2.roomid) t"
        print(sql_)
        self.cursor.execute(sql_)
        print(ret)
        ret += self.cursor.fetchall()
        print(ret)
        return ret

    # 删除预订信息：将 success 修改为 false
    def delete_book_info_by_serial_num(self, serial_num):
        sql_ = f"update sequenceinfo set success=false where serialnum='{serial_num}'"
        self.cursor.execute(sql_)
        # return self.connect.commit()

    # 办理入住信息
    def live_in(self, serial_num):
        sql_ = f"call live_in_pro('{serial_num}')"
        self.cursor.execute(sql_)

    # 按照客人名称查询该客人的信息
    def select_customer_information_by_name(self, name):
        sql_ = f"select cid, name, sex, tel from customer where name = '{name}'"
        self.cursor.execute(sql_)
        t0 = self.cursor.fetchall()
        if len(t0) == 0:
            return []
        sql_ = "select c.cid, c.name, c.sex, c.tel, r.roomID, r.type, r.price, se.checkin, se.checkout " \
               "from customer c, room r, stay st, sequenceInfo se " \
               f"where c.cid = st.cid and st.serialNum = se.serialNum and st.roomID = r.roomID and c.name = '{name}'"
        self.cursor.execute(sql_)
        t = self.cursor.fetchall()
        if len(t) == 0:
            return [[t0[0][0], t0[0][1], t0[0][2], t0[0][3], "None", "None", "None", "None", "None"]]
        return t

    # 查询某日收入情况
    def select_date_income(self, currDate):
        sql_ = f"select wantdate, sum(custsum), sum(prices)," \
               f" sum(singlein), sum(singlesum)," \
               f" sum(doublein), sum(doublesum)," \
               f" sum(bigin), sum(bigsum) " \
               f"from financial natural join sequenceinfo " \
               f"where wantdate='{currDate}' and success=true and haspay = true"

        self.cursor.execute(sql_)
        return self.cursor.fetchall()

    # 查询某月收入情况
    def select_month_income(self, year, month):
        sql_ = f"select '{year}-{month}', sum(custsum), sum(prices), " \
               f"sum(singlein), sum(singlesum), " \
               f"sum(doublein), sum(doublesum), " \
               f"sum(bigin), sum(bigsum) " \
               f"from financial natural join sequenceinfo " \
               f"where year(wantdate) = '{year}' and month(wantdate) = '{month}' " \
               f"and success=true and haspay = true"

        self.cursor.execute(sql_)
        return self.cursor.fetchall()

    # 查询某年收入情况
    def select_year_income(self, year):
        sql_ = f"select '{year}', sum(custsum), sum(prices), " \
               f"sum(singlein), sum(singlesum), " \
               f"sum(doublein), sum(doublesum), " \
               f"sum(bigin), sum(bigsum) " \
               f"from financial natural join sequenceinfo " \
               f"where year(wantdate) = '{year}' and success=true and haspay =true"

        self.cursor.execute(sql_)
        return self.cursor.fetchall()

    # 查询某日剩余的某房间类型的房间数量
    # 如果一个房间是1日check-in, 2日check-out, 则认为1日该房已被入住2日该房未被入住
    def select_left_rooms_count(self, currDate):
        sql_ = "select type, count(type) from room left join " \
               "(select * from stay natural join sequenceinfo " \
               f"where wantdate='{currDate}') t on room.roomid=t.roomid " \
               "where success is null or success=0 group by type"
        self.cursor.execute(sql_)
        return self.cursor.fetchall()

    # 查询某类型房间单价
    def select_price_of_room_type(self, room_type):
        sql_ = f"select distinct price from room where type = '{room_type}'"
        self.cursor.execute(sql_)
        price = self.cursor.fetchall()
        return price[0][0]

    def select_left_rooms_info(self, room_type, currDate):
        '''
        查询某日某类型空闲房间信息
        :param room_type: 房间类型（若为空则查询所有）
        :param currDate: 当前日期，用于界定房间状态
        :return: (roomID, type, price, roomState(空闲), null, null, null, null, null)
        '''
        if room_type == '':
            sql_ = "select r.roomID, r.type, r.price, '空闲', null, null, null, null, null " \
                   f"from room r where roomID not in " \
                   f"(select roomID from stay natural join sequenceInfo where wantDate='{currDate}')"
        else:
            sql_ = "select r.roomID, r.type, r.price, '空闲', null, null, null, null, null " \
                   f"from room r where r.type='{room_type}' and roomID not in " \
                   f"(select roomID from stay natural join sequenceInfo where wantDate='{currDate}')"

        self.cursor.execute(sql_)
        return self.cursor.fetchall()

    def select_room_information_by_id(self, roomid, currDate):
        '''
        :param roomid: 房间号
        :param currDate: 当前日期，用于界定房间状态
        :return: (roomID, type, price, roomState, customerName, customerTel, leaderName, wantDate, leaderTel)
        :roomState: 空闲 / 被预定 / 已入住
        '''
        sql_ = "select r.roomid, r.type, r.price, c.name, se.wantDate, c.tel " \
               "from customer c, room r, stay st, sequenceInfo se " \
               f"where r.roomid={roomid} and st.roomid=r.roomid and se.serialNum = st.serialNum and " \
               f"se.leadercid = c.cid and se.wantDate = '{currDate}'"
        self.cursor.execute(sql_)
        t = self.cursor.fetchall()
        if len(t) == 0:  # 该房间空闲，没有被居住也没有被预定
            sql_ = f"select roomid, type, price from room where roomid={roomid}"
            self.cursor.execute(sql_)
            t1 = self.cursor.fetchall()
            #       roomid     type     price    state  customer tel   leader   wantDate  tel
            return [[t1[0][0], t1[0][1], t1[0][2], "空闲", "None", "None", "None", "None", "None"]]
        sql_ = "select r.roomid, r.type, r.price, c.name, c.tel, c2.name, se.wantDate, c2.tel " \
               "from customer c, room r, stay st, sequenceInfo se, customer c2 " \
               f"where r.roomid={roomid} and st.roomid=r.roomid and " \
               "se.serialNum = st.serialNum and se.leadercid=c2.cid and st.cid = c.cid and " \
               f"se.checkin <= '{currDate}' and se.checkout > '{currDate}'"
        self.cursor.execute(sql_)
        t3 = self.cursor.fetchall()
        if len(t3) == 0:  # 该房间被预定，但还没有被居住
            return [[t[0][0], t[0][1], t[0][2], "被预定", "None", "None", t[0][3], t[0][4], t[0][5]]]
        ret = []
        for i in range(len(t3)):
            ret.append([t3[0][0], t3[0][1], t3[0][2], "已入住", t3[0][3], t3[0][4], t3[0][5], t3[0][6], t3[0][7]])
        return ret

    # 结账/团体结账， 给定一个流水号，输出该流水号对应的所有房价之和
    def settle_account(self, _serialNum):
        sql_ = "select sum(price) " \
               "from( " \
               "select distinct r.roomID, r.price from room r, sequenceInfo se, stay st " \
               f"where r.roomID = st.roomID and se.serialNum = st.serialNum and se.serialNum = '{_serialNum}') t;"
        self.cursor.execute(sql_)
        totalPrice = self.cursor.fetchall()
        return totalPrice[0][0]

    def update_Checkout(self, cid, today):
        sql_ = f"update sequenceInfo set checkout=NOW() where leaderCid='{cid}' and checkout is null " \
               f"and wantdate <= '{today}' and success=true and hasPay=True;"
        self.cursor.execute(sql_)
        self.connect.commit()

    def insert_sequenceInfo(self, wantDate, leadercid):
        sql_ = f"insert into sequenceinfo (wantDate, leaderCid) values('{wantDate}', '{leadercid}')"
        self.cursor.execute(sql_)
        self.connect.commit()
        sql_ = f"select max(serialNum) from sequenceinfo;"
        self.cursor.execute(sql_)
        serialNum = self.cursor.fetchall()
        # print("insert serialInfo: ", serialNum)
        return serialNum[0][0]

    def insert_into_stay(self, serialNum, date, paras):
        """
        paras = ("type, cid1, 姓名1, sex1, tel1, cid2, 姓名2, sex2, tel2, 房间号")
        1. find a room with correct type;
        2. insert into stay
        :return a string :roomID
        """
        sql_ = f"select roomID from room  where room.type='{paras[0]}' and room.roomID not in (" \
               f"   select roomID from sequenceInfo natural join stay " \
               f"   where wantDate='{date}'" \
               f")limit 1"
        self.cursor.execute(sql_)
        room = self.cursor.fetchall()
        if len(room) == 0:
            raise RuntimeError("No room. check the remain")
        sql_ = f"insert into stay values ({serialNum}, {paras[1]}, '{room[0][0]}')"
        self.cursor.execute(sql_)
        self.connect.commit()
        if paras[0] == "双人房" or paras[0] == "大床房":
            sql_ = f"insert into stay values ({serialNum}, {paras[5]}, '{room[0][0]}')"
            self.cursor.execute(sql_)
            self.connect.commit()
        return room[0][0]


    def __del__(self):
        if self.cursor is not None:
            self.cursor.close()  # 先关闭游标
        if self.cursor is not None:
            self.connect.close()  # 再关闭数据库连接
